Excel BI - Excel Challenge 762

excel-challenges
excel-formulas
🔰 Answer Expected Data Alphabet Value Q85R563Z98 A R673Q98 B AB5622BB924Z1 C
Published

March 24, 2026

Illustration for Excel BI - Excel Challenge 762

Challenge Description

🔰 Answer Expected Data Alphabet Value Q85R563Z98 A R673Q98 B AB5622BB924Z1 C

Solutions

library(tidyverse)
library(readxl)

path = "Excel/700-799/762/762 Alphabets Pivot.xlsx"
input = read_excel(path, range = "A2:A6")
test  = read_excel(path, range = "C2:D8")

result = input %>%
  mutate(fragment = str_extract_all(Data, "[A-Za-z]+\\d+")) %>%
  unnest_longer(fragment) %>%
  mutate(
    letters = str_extract(fragment, "[A-Za-z]+"),
    digits = as.numeric(str_extract(fragment, "\\d+")),
    Alphabet = str_split(letters, "")
  ) %>%
  unnest(Alphabet) %>%
  summarise(Value = sum(digits / str_length(letters)), .by = Alphabet) %>%
  arrange(Alphabet)

all.equal(result, test)
#># [1] TRUE
  • Logic: Read the workbook ranges needed for the challenge; Derive the required intermediate columns; Parse the packed text or string structure; Aggregate or rank the data at the required grouping level.
  • Strengths: The solution stays close to the text pattern itself, which makes the extraction logic easy to audit.
  • Areas for Improvement: The solution assumes the workbook layout and selected ranges remain stable, so any structural change in the sheet would require small adjustments.
  • Gem: A small number of well-targeted text patterns does most of the heavy lifting.
import pandas as pd
import re

path = "700-799/762/762 Alphabets Pivot.xlsx"

input = pd.read_excel(path, usecols="A", skiprows=1, nrows=4)
test = pd.read_excel(path, usecols="C:D", skiprows=1, nrows=7)

input['fragment'] = input['Data'].apply(lambda x: re.findall(r"[A-Za-z]+\d+", str(x) if pd.notnull(x) else ""))
input = input.explode('fragment')
input['letters'] = input['fragment'].apply(lambda x: re.search(r"[A-Za-z]+", x).group())
input['digits'] = input['fragment'].apply(lambda x: int(re.search(r"\d+", x).group()))
input['Value'] = input['digits'] / input['letters'].apply(len)
input['Alphabet'] = input['letters'].apply(list)
input = input.explode('Alphabet')

result = input.groupby('Alphabet')['Value'].sum().astype(int)
result = result.reset_index()

print(result.equals(test))

The Python version follows the same grouped logic and keeps the transformation explicit in a dataframe pipeline.

Difficulty Level

Medium

The individual steps are manageable, but the correct transformation pattern is not obvious from the raw data.